# -*- coding: utf-8 -*-
# @Time    : 2020/11/5 10:32
# @Author  : liuwenfan
# @File    : report_service.py
# @Software: PyCharm
import datetime

from sqlalchemy import func, desc

from application import db
from application.libs import const
from application.models.models import Report, Case, JobTask
from application.settings import Config


def healthy():
    """
    健康程度
    最近24小时执行的次数， 大于百分之九十五算成功， 小于百分至九十五算是失败， 返回成功的条数和失败的条数
    :return: 健康程度数组
    """
    global dict
    # sql = """
    #      SELECT succ,total FROM report where report.create_time >=  NOW() - interval 168 hour;
    #  """
    # result = db.session.execute(sql).fetchall()
    cur_date = (datetime.date.today() - datetime.timedelta(days=const.DAYS)).strftime("%Y-%m-%d %H:%M:%S")
    result = db.session.query(Report.succ, Report.total).filter(
        Report.create_time >= cur_date).all()
    scount = 0
    sfail = 0
    if result:
        for item in result:
            if item.total == 0:
                continue
            if item.succ / item.total >= 0.95:
                scount = scount + 1
            else:
                sfail = sfail + 1
    l = [
        {"name": const.STR_SUCCESS, "value": scount},
        {"name": const.STR_FAIL, "value": sfail}
    ]
    return l


def casenum():
    """
    CASE 数量
    现在已有项目所有case排行榜，按项目
    :return:
    """
    # sql = """
    #            SELECT project_name,case_num FROM `case` limit 10;
    #       """
    # result1 = db.session.execute(sql).fetchall()
    result = db.session.query(Case.project_name.label("project_name"),
                              func.sum(Case.case_num).label("case_num")).group_by(Case.project_name).order_by(
        desc("case_num")).limit(10).all()
    ret = []
    for item in result:
        dict = {"date": item.project_name,
                "value": int(item.case_num)}
        ret.append(dict)
    cn_list = [
        {
            "color": "#67C23A",
            "counter": sorted(ret, key=lambda d: d["value"], reverse=True),
            "name": const.TOTAL_CASE_NUM,
            "type": const.TOTAL_CASE_BAR
        }
    ]
    return cn_list


def bugnum():
    """
    bug排名
    最近24小时出现bug的排行榜  按项目
    :return:
    """
    # sql = """
    #         SELECT  report.id as r_id,`case`.id as c_id,report.fail as r_fail,`case`.group_name as c_group_name,report.total as r_total,report.create_time as r_create_time
    #         FROM report
    #         INNER JOIN `case`
    #         ON report.case_id=`case`.id
    #         where report.create_time >=  NOW() - interval 168 hour;
    #
    #         select c.group_name,COUNT(r.fail) as bug_nums
    #         from `report` as r,`case` as c
    #         where r.case_id=c.id
    #         GROUP BY c.group_name
    #         ORDER BY bug_nums asc

    #       """
    # result = db.session.execute(sql).fetchall()
    cur_date = (datetime.date.today() - datetime.timedelta(days=const.DAYS)).strftime("%Y-%m-%d %H:%M:%S")
    result = db.session.query(Case.group_name.label("c_group_name"), func.count(Report.fail).label("r_fail")).join(Case,
                                                                                                                   Report.case_id == Case.id).filter(
        Report.create_time >= cur_date).group_by(Case.group_name).order_by(func.count(Report.fail).desc()).all()
    ret = []
    if result:
        for item in result:
            if item.r_fail == 0:
                continue
            i_dict = {
                "value": item.r_fail,
                "date": item.c_group_name
            }

            ret.append(i_dict)
    bn_list = [
        {
            "color": "#67C23A",
            "counter": sorted(ret, key=lambda d: d["value"], reverse=True),
            "name": const.BUG_SORT_Y,
            "type": const.TOTAL_CASE_BAR
        }
    ]
    return bn_list


def implementcase():
    """
    用例数
    24小时之内case的执行数量，通过和不通过，返回这24小时成功的总和和失败的总和，不与case表做关联查询，只返回sum(succ),sum(fail)
    :return:
    """
    # sql = """
    #         SELECT SUM(report.succ) as sum_report_succ, SUM(report.fail) as sum_report_fail
    #         FROM report
    #         where report.create_time >=  NOW() - interval 168 hour;
    #       """
    # result = db.session.execute(sql).fetchall()
    cur_date = (datetime.date.today() - datetime.timedelta(days=const.DAYS)).strftime("%Y-%m-%d %H:%M:%S")
    result = db.session.query(func.SUM(Report.succ).label("sum_report_succ"),
                              func.SUM(Report.fail).label("sum_report_fail")).filter(
        Report.create_time >= cur_date).all()
    s_sum_report = 0
    f_sum_report = 0
    if result:
        if result[0].sum_report_succ is not None and result[0].sum_report_fail is not None:
            s_sum_report = int(result[0].sum_report_succ)
            f_sum_report = int(result[0].sum_report_fail)
    i_list = [
        {"name": const.STR_SUCCESS, "value": s_sum_report},
        {"name": const.STR_FAIL, "value": f_sum_report}
    ]
    return i_list


def build_count():
    """
    仪表盘数据：构建统计
    :return:
    """
    # sql = """
    #     select time_,count(-1) as count_ from (
    #     select
    #     time_,
    #     count(-1) count_
    #     from (
    #     select DATE_FORMAT(create_time,'%m-%d') time_,build_id,task_id from
    #         (
    #         SELECT report.task_id,report.build_id,`case`.group_name,report.create_time
    #                                 FROM report
    #                                 INNER JOIN `case`
    #                                 ON report.case_id=`case`.id
    #         ) a
    # ) t group by time_,build_id,task_id
    #
    # ) t1 GROUP BY time_
    # """
    # result1 = db.session.execute(sql).fetchall()
    a_result = db.session.query(Report.task_id.label("r_task_id"),
                                Report.build_id.label("r_build_id"),
                                Case.group_name.label("c_group_name"),
                                Report.create_time.label("r_create_time")).join(Case,
                                                                                Report.case_id == Case.id).subquery()
    b_result = db.session.query(func.DATE_FORMAT(a_result.c.r_create_time, '%y-%m-%d').label("time_"),
                                a_result.c.r_build_id.label("r_build_id_b"),
                                a_result.c.r_task_id.label("r_task_id_b")).subquery()
    c_result = db.session.query(b_result.c.time_.label("time_md"),
                                func.count(-1).label("count_")).group_by(b_result.c.time_, b_result.c.r_build_id_b,
                                                                         b_result.c.r_task_id_b).subquery()
    result = db.session.query(c_result.c.time_md, func.count(-1).label("count_md")).group_by(c_result.c.time_md).all()
    bc_list = []
    if result:
        for item in result:
            bc_list.append({"date": item.time_md[3:], "value": item.count_md})
    data = [{
        "color": "#67C23A",
        "name": const.BUILD_COUNT_Y,
        "type": const.BUILD_COUNT_LINE,
        "counter": bc_list
    }]
    return data


def report_record():
    """
    仪表盘数据：构建记录
    :return:
    """
    # sql = """
    # SELECT `case`.project_name as c_project_name,`case`.case_name as c_case_name,`case`.group_name as c_group_name,
    # report.info as r_info,report.create_time as r_create_time
    #         FROM report
    #         INNER JOIN `case`
    #         ON report.case_id=`case`.id
    #         ORDER BY report.create_time DESC
    #         LIMIT 10
    # """
    # result = db.session.execute(sql).fetchall()
    # result = db.session.query(Case.project_name.label("c_project_name"), Case.case_name.label("c_case_name"),
    #                           Case.group_name.label("c_group_name"), Report.info.label("r_info"),
    #                           Report.create_time.label("r_create_time")).join(Case, Report.case_id == Case.id).order_by(
    #     Report.create_time.desc()).limit(10).all()

    result = db.session.query(Report.create_time.label("r_create_time"), JobTask.task_name.label("j_task_name"),
                              JobTask.id.label("j_id"), Report.build_id.label("r_build_id")).join(JobTask,
                                                                                                  Report.task_id == JobTask.id).group_by(
        JobTask.id, Report.build_id).order_by(Report.create_time.desc()).limit(10).all()

    r_list = []
    if result:
        for item in result:
            i_dic = {
                "case_name": item.j_task_name,
                "info": "http://%s/#/CITest/ci_report?task_id=%s&build_id=%s" % (
                    Config.DOMAIN, item.j_id, item.r_build_id),
                "date": str(item.r_create_time)
            }
            r_list.append(i_dic)
    return r_list


def get_report_data(task_id, build_id):
    """
    集成测试报告
    :param task_id: 任务id
    :param build_id: BUILD_ID
    :return:
    """
    ret = db.session.query(Report).filter(Report.task_id == task_id, Report.build_id == build_id).all()
    web_ui = []
    app_ui = []
    yapi = []
    task_name, job_name = get_job_name(task_id)
    if 0 in [i.total for i in ret] or ret == []:
        rate = -1
    else:
        rate = sum([i.succ for i in ret]) / sum([i.total for i in ret])
    cases = {}
    for i in ret:
        cases["case_id"] = i.case_id
        cases["case_name"] = get_case_name(i.case_id)
        cases["total"] = i.total
        cases["succ"] = i.succ
        cases["fail"] = i.fail
        cases["err"] = i.err
        cases["info"] = i.info
        if i.type == 1:
            web_ui.append(cases.copy())
        elif i.type == 2:
            app_ui.append(cases.copy())
        elif i.type == 3:
            yapi.append(cases.copy())
        cases.clear()
    data = {"rate": round(rate, 4), "task_name": task_name, "job_name": job_name, "web_ui": web_ui, "app_ui": app_ui,
            "yapi": yapi}
    return data


def get_case_name(case_id):
    ret = db.session.query(Case.case_name).filter(Case.id == case_id).one()
    return ret


def get_job_name(task_id):
    ret = db.session.query(JobTask.task_name, JobTask.job_name).filter(JobTask.id == task_id).one()
    return ret
